# 新增mysql_utils.py模块
import pymysql
from datetime import datetime

class MySQLManager:
    def __init__(self):
        self.db_config = {
            'host': 'localhost',
            'port': 3306,
            'user': 'root',
            'password': 'root',
            'database': 'voice_logs',
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor
        }
        
    def get_connection(self):
        return pymysql.connect(**self.db_config)

    def create_table(self):
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                sql = """CREATE TABLE IF NOT EXISTS interactions (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        text TEXT NOT NULL,
                        answer TEXT NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )"""
                cursor.execute(sql)
                conn.commit()

    def log_interaction(self, text, answer):
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                sql = "INSERT INTO interactions (text, answer) VALUES (%s, %s)"
                cursor.execute(sql, (text, answer))
                conn.commit()
                new_record = {'text': text, 'answer': answer}
                socketio.emit('new_record', new_record)  # 推送新增数据
                return True
                
    def get_all_records(self):
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute("SELECT * FROM interactions ORDER BY created_at DESC")
                return cursor.fetchall()
                
    def get_recent_10(self):
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute("SELECT * FROM interactions ORDER BY created_at DESC LIMIT 10")
                return cursor.fetchall()

# 初始化数据库
db_manager = MySQLManager()
db_manager.create_table()